package crud.util;


import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import lombok.Data;

import java.util.*;


public class SqlParse {

    @Data
    public static class SqlStructure {
        //数据库类型
        String dbType;
        // 原始SQL语句 [SELECT ID,HOSTIP,PASSWORD PWD FROM SYNITIL.OSITEM]
        String originalSql;
        // 优化后的SQL语句 [select ID, HOSTIP, PASSWORD as PWD from SYNITIL.OSITEM]
        String optimizedSql;
        // 包含的库 库
        Collection<String> includeDB;
        // 包含的表 库.表
        Collection<String> includeTables;
        // 包含的权限 ["SELECT","UPDATE","DELETE","INSERT","DROP,"MERGE","CREATE","ALTER","CREATEINDEX","DROPINDEX","REFERENCED"]
        Collection<String> includePermission;
        // 包含的字段 库.表.字段
        Collection<String> includeColumns;
        // 包含的条件字段 库.表.字段
        Collection<String> includeConditions;
        // 包含的分组聚合字段 库.表.字段
        Collection<String> includeGroupByColumns;
        // 包含的分组排序字段 库.表.字段
        Collection<String> includeOrderByColumns;
        // 包含的参数
        Collection<String> includeParameters;
    }

    static public Collection<String> parseIncludeDB(String curDB, SchemaStatVisitor statVisitor) {
        Collection<String> includeDB = new HashSet<>();
        includeDB.add(curDB);

        java.util.Map<TableStat.Name, TableStat> tables = statVisitor.getTables();
        for (TableStat.Name key : tables.keySet()) {
            String tableName = key.getName();
            List<String> tableNameSplit = StrUtil.split(tableName, ".");
            if (tableNameSplit.size() == 2) {
                String dbName = tableNameSplit.get(0);
                includeDB.add(dbName.toUpperCase());
            }
        }
        return includeDB;
    }

    static public Collection<String> parseIncludeTable(String curDB, SchemaStatVisitor statVisitor) {
        Collection<String> includeTable = new HashSet<>();

        java.util.Map<TableStat.Name, TableStat> tables = statVisitor.getTables();
        for (TableStat.Name key : tables.keySet()) {
            String tableName = key.getName();
            List<String> tableNameSplit = StrUtil.split(tableName, ".");
            if (tableNameSplit.size() == 2) {
                includeTable.add(tableName.toUpperCase());
            } else {
                String dbTable = curDB + "." + tableName;
                includeTable.add(dbTable.toUpperCase());
            }
        }
        return includeTable;
    }

    static public Collection<String> parseIncludePermisson(SchemaStatVisitor statVisitor) {
        Collection<String> includePermisson = new HashSet<>();
        java.util.Map<TableStat.Name, TableStat> tables = statVisitor.getTables();
        for (TableStat.Name key : tables.keySet()) {
            TableStat table = tables.get(key);
            if (table.getSelectCount() > 0) {
                includePermisson.add("SELECT");
            } else if (table.getUpdateCount() > 0) {
                includePermisson.add("UPDATE");
            } else if (table.getDeleteCount() > 0) {
                includePermisson.add("DELETE");
            } else if (table.getInsertCount() > 0) {
                includePermisson.add("INSERT");
            } else if (table.getDropCount() > 0) {
                includePermisson.add("DROP");
            } else if (table.getMergeCount() > 0) {
                includePermisson.add("MERGE");
            } else if (table.getCreateCount() > 0) {
                includePermisson.add("CREATE");
            } else if (table.getAlterCount() > 0) {
                includePermisson.add("ALTER");
            } else if (table.getCreateIndexCount() > 0) {
                includePermisson.add("CREATEINDEX");
            } else if (table.getDropIndexCount() > 0) {
                includePermisson.add("DROPINDEX");
            } else if (table.getReferencedCount() > 0) {
                includePermisson.add("REFERENCED");
            }
        }
        return includePermisson;
    }

    static public Collection<String> parseIncludeField(String curDB, SchemaStatVisitor statVisitor) {
        Collection<String> includeField = new HashSet<>();

        Collection<TableStat.Column> columns = statVisitor.getColumns();
        for (TableStat.Column field : columns) {
            String fieldName = field.getFullName();
            List<String> fieldNameSplit = StrUtil.split(fieldName, ".");
            if (fieldNameSplit.size() == 3) {
                includeField.add(fieldName.toUpperCase());
            } else {
                String dbTable = curDB + "." + fieldName;
                includeField.add(dbTable.toUpperCase());
            }
        }
        return includeField;
    }

    static public Collection<String> parseIncludeCondition(String curDB, SchemaStatVisitor statVisitor) {
        Collection<String> includeCondition = new HashSet<>();
        Collection<TableStat.Condition> conditions = statVisitor.getConditions();
        for (TableStat.Condition condition : conditions) {
            String fieldName = condition.getColumn().toString();
            List<String> fieldNameSplit = StrUtil.split(fieldName, ".");
            if (fieldNameSplit.size() == 3) {
                fieldName = fieldName.toUpperCase();
            } else {
                fieldName = curDB + "." + fieldName.toUpperCase();
            }

            String exp = String.format("%s %s %s", fieldName, condition.getOperator(), condition.getValues());
            includeCondition.add(exp);
        }
        return includeCondition;
    }

    static public Collection<String> parseGroupByField(String curDB, SchemaStatVisitor statVisitor) {
        Collection<String> includeField = new HashSet<>();

        Collection<TableStat.Column> columns = statVisitor.getGroupByColumns();
        for (TableStat.Column field : columns) {
            String fieldName = field.getFullName();
            List<String> fieldNameSplit = StrUtil.split(fieldName, ".");
            if (fieldNameSplit.size() == 3) {
                includeField.add(fieldName.toUpperCase());
            } else {
                String dbTable = curDB + "." + fieldName;
                includeField.add(dbTable.toUpperCase());
            }
        }
        return includeField;
    }

    static public Collection<String> parseOrderByField(String curDB, SchemaStatVisitor statVisitor) {
        Collection<String> includeField = new HashSet<>();

        Collection<TableStat.Column> columns = statVisitor.getOrderByColumns();
        for (TableStat.Column field : columns) {
            String fieldName = field.getFullName();
            List<String> fieldNameSplit = StrUtil.split(fieldName, ".");
            if (fieldNameSplit.size() == 3) {
                includeField.add(fieldName.toUpperCase());
            } else {
                String dbTable = curDB + "." + fieldName;
                includeField.add(dbTable.toUpperCase());
            }
        }
        return includeField;
    }

    //SQL语法树解析
    static public List<SqlStructure> doParse(String curDB, String dbType, String sql) {
        List<SqlStructure> resList = new ArrayList<>();

        List<SQLStatement> stmtList;
        stmtList = SQLUtils.parseStatements(sql, dbType);
        for (SQLStatement stmt : stmtList) {
            SqlStructure res = new SqlStructure();
            res.setDbType(dbType);
            SchemaStatVisitor statVisitor = SQLUtils.createSchemaStatVisitor(DbType.of(dbType));
            stmt.accept(statVisitor);
            res.setOptimizedSql(stmt.toLowerCaseString().replace("\n", " "));
            res.setIncludeDB(parseIncludeDB(curDB, statVisitor));
            res.setIncludeTables(parseIncludeTable(curDB, statVisitor));
            res.setIncludePermission(parseIncludePermisson(statVisitor));
            res.setIncludeColumns(parseIncludeField(curDB, statVisitor));
            res.setIncludeConditions(parseIncludeCondition(curDB, statVisitor));
            res.setIncludeGroupByColumns(parseGroupByField(curDB, statVisitor));
            res.setIncludeOrderByColumns(parseOrderByField(curDB, statVisitor));
            resList.add(res);
        }
        return resList;
    }

}
